﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DataAccessLayer
{
    public class DiemDanhDAL : DBConnection
    {
        public List<DiemDanhEntity> getAll()
        {
            List<DiemDanhEntity> lst = new List<DiemDanhEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("diem_danh_getAll", con);
                cmd.CommandType = CommandType.StoredProcedure;

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    DiemDanhEntity obj = new DiemDanhEntity();
                    obj.MaBuoiHoc = Convert.ToInt32(dr["MaBuoiHoc"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.Time = Convert.ToDateTime(dr["Time"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public List<DiemDanhEntity> getByBuoiHocId(int _id)
        {
            List<DiemDanhEntity> lst = new List<DiemDanhEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("diem_danh_getByBuoiHocId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maBuoiHoc", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    DiemDanhEntity obj = new DiemDanhEntity();
                    obj.MaBuoiHoc = Convert.ToInt32(dr["MaBuoiHoc"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.Time = Convert.ToDateTime(dr["Time"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public List<DiemDanhEntity> getByHocVien_KhoaHocId(string _maHocVien, int _maKhoaHoc)
        {
            List<DiemDanhEntity> lst = new List<DiemDanhEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("diem_danh_getByHocVien_KhoaHoc", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _maHocVien);
                cmd.Parameters.AddWithValue("maKhoaHoc", _maKhoaHoc);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    DiemDanhEntity obj = new DiemDanhEntity();
                    obj.MaBuoiHoc = Convert.ToInt32(dr["MaBuoiHoc"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.Time = Convert.ToDateTime(dr["Time"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public DiemDanhEntity getByBuoiHoc_HocVien(int _maBuoiHoc, string _maHocVien)
        {
            DiemDanhEntity obj = new DiemDanhEntity();
            try
            {
                MySqlCommand cmd = new MySqlCommand("diem_danh_getByBuoiHoc_HocVien", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maBuoiHoc", _maBuoiHoc);
                cmd.Parameters.AddWithValue("maHocVien", _maHocVien);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.Read())
                {
                    obj.MaBuoiHoc = Convert.ToInt32(dr["MaBuoiHoc"]);
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.Time = Convert.ToDateTime(dr["Time"]);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return obj;
        }

        public int addDiemDanh(DiemDanhEntity _obj)
        {
            int result = 0;
            try
            {

                MySqlCommand cmd = new MySqlCommand("lop_hoc_insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maBuoiHoc", _obj.MaBuoiHoc);
                cmd.Parameters.AddWithValue("maHocVien", _obj.MaHocVien);

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int deleteDiemDanh(int _maBuoiHoc, string _maHocVien)
        {
            int result = 0;
            try
            {
                MySqlCommand cmd = new MySqlCommand("diem_danh_delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maBuoiHoc", _maBuoiHoc);
                cmd.Parameters.AddWithValue("maHocVien", _maHocVien);

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }
    }
}
